package jvm.jtacck.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import jvm.jtacck.db.records.*;

/**
 * PostgresHandler represents a connection to a database containing the
 * following schema:
 * 
 * hospital (hospital_id, name, address) client_site (client_id, name,
 * hospital_id, instructions, phone_number) user_type (type_id, name,
 * description) users (username, password, fname, lname, site_id, type_id)
 * courier (username, route, lat_loc, long_loc, nextDest) specimen (barcode,
 * description, requirements) item (item_id, name, cap_color) status (status_id,
 * name, description) contains (barcode, item_id) shipping_info (barcode,
 * shipper, destination_id, status_id integer, courier_id varchar(20))
 * 
 * PostgresHandler is an implementation of DatabaseHandler that is specific to
 * using a PostgreSQL Database, as the database management system used for the
 * jtacck tracking system.
 * 
 * @author Chris Gamble
 * 
 */
public class PostgresHandler implements DatabaseHandler {
    private Connection dbConn;
    private String dbAddress;
    private String user;
    private String password;

    // SQL Queries
    private static final String ADD_CLIENT_SITE = "INSERT INTO client_site VALUES ( DEFAULT, ?, ?, ?, ?);";
    private static final String ADD_COURIER = "INSERT INTO courier VALUES ( ?, ?, ?, ?, ?);";
    private static final String ADD_HOSPITAL = "INSERT INTO hospital VALUES ( DEFAULT, ?, ?);";
    private static final String ADD_ITEM = "INSERT INTO item VALUES ( DEFAULT, ?, ?);";
    private static final String ADD_TO_CONTAINS = "INSERT INTO contains VALUES ( ?, ?);";
    private static final String ADD_SHIPPING_INFO = "INSERT INTO shipping_info VALUES ( ?, ?, ?, ?, ?, 'now', 'now')";
    private static final String ADD_SPECIMEN = "INSERT INTO specimen VALUES ( ?, ?, ?)";
    private static final String ADD_STATUS = "INSERT INTO status VALUES ( DEFAULT, ?, ?);";
    private static final String ADD_USER = "INSERT INTO users VALUES ( ?, ?, ?, ?, ?, ?);";
    private static final String ADD_USER_TYPE = "INSERT INTO user_type VALUES ( DEFAULT, ?, ?);";
    private static final String UPDATE_CHECKLIST_PAIR = "UPDATE checklist "
            + "SET starting = ?, ending = ? " + "WHERE checklist_id = 1;";
    private static final String UPDATE_CLIENT_SITE = "UPDATE client_site "
            + "SET name = ?, hospital_id = ?, instructions = ?, phone_number = ? "
            + "WHERE client_id = ?;";
    private static final String UPDATE_COURIER = "UPDATE courier "
            + "SET route = ?, lat_loc = ?, long_loc = ?, next_destination = ? "
            + "WHERE username = ?;";
    private static final String UPDATE_HOSPITAL = "UPDATE hospital "
            + "SET name = ?, address = ? " + "WHERE hospital_id = ?;";
    private static final String UPDATE_ITEM = "UPDATE item "
            + "SET name = ?, cap_color = ? " + "WHERE item_id = ?;";
    private static final String UPDATE_SHIPPING_INFO = "UPDATE shipping_info "
            + "SET shipper = ?, destination_id = ?, status_id = ?, courier_id = ?, time_updated = 'now' "
            + "WHERE barcode = ?;";
    private static final String UPDATE_SPECIMEN = "UPDATE specimen "
            + "SET description = ?, requirements = ? " + "WHERE barcode = ?;";
    private static final String UPDATE_STATUS = "UPDATE status "
            + "SET name = ?, description = ? " + "WHERE status_id = ?;";
    private static final String UPDATE_USER = "UPDATE users "
            + "SET password = ?, fname = ?, lname = ?, site_id = ?, type_id = ? "
            + "WHERE username = ?;";
    private static final String UPDATE_USER_TYPE = "UPDATE user_type "
            + "SET name = ?, description = ? " + "WHERE type_id = ?;";
    private static final String GET_CHECKLISTS = "SELECT * FROM checklist WHERE checklist_id = 1;";
    private static final String GET_CLIENT_SITE = "SELECT * FROM client_site WHERE client_id = ?;";
    private static final String GET_COURIER = "SELECT * FROM courier WHERE username = ?;";
    private static final String GET_HOSPITAL = "SELECT * FROM hospital WHERE hospital_id = ?;";
    private static final String GET_ITEM = "SELECT * FROM item WHERE item_id = ?;";
    private static final String GET_SHIPPING_INFO = "SELECT * FROM shipping_info WHERE barcode = ?;";
    private static final String GET_SPECIMEN = "SELECT * FROM specimen s WHERE s.barcode = ?;";
    private static final String GET_CONTENTS = "SELECT * FROM contains c WHERE c.barcode = ?;";
    private static final String GET_STATUS = "SELECT * FROM status WHERE status_id = ?;";
    private static final String GET_USER = "SELECT * FROM users WHERE username = ?;";
    private static final String GET_USER_TYPE = "SELECT * FROM user_type WHERE type_id = ?;";
    private static final String GET_ALL_CLIENT_SITES = "SELECT * FROM client_site;";
    private static final String GET_ALL_COURIERS = "SELECT * FROM courier;";
    private static final String GET_ALL_HOSPITALS = "SELECT * FROM hospital;";
    private static final String GET_ALL_ITEMS = "SELECT * FROM item;";
    private static final String GET_ALL_SHIPPING_INFOS = "SELECT * FROM shipping_info si;";
    private static final String GET_ALL_SPECIMENS = "SELECT * FROM specimen;";
    private static final String GET_ALL_STATUSES = "SELECT * FROM status;";
    private static final String GET_ALL_USERS = "SELECT * FROM users;";
    private static final String GET_ALL_USER_TYPES = "SELECT * FROM user_type;";

    private static final String REMOVE_CLIENT_SITE = "DELETE FROM client_site "
            + "WHERE client_id = ?;";
    private static final String REMOVE_COURIER = "DELETE FROM courier "
            + "WHERE username = ?;";
    private static final String REMOVE_HOSPITAL = "DELETE FROM hospital "
            + "WHERE hospital_id = ?;";
    private static final String REMOVE_ITEM = "DELETE FROM item "
            + "WHERE item_id = ?;";
    private static final String REMOVE_SHIPPING_INFO = "DELETE FROM shipping_info "
            + "WHERE barcode = ?;";
    private static final String REMOVE_SPECIMEN = "DELETE FROM specimen "
            + "WHERE barcode = ? ";
    private static final String REMOVE_STATUS = "DELETE FROM status "
            + "WHERE status_id = ?;";
    private static final String REMOVE_USER = "DELETE FROM users "
            + "WHERE username = ?;";
    private static final String REMOVE_USER_TYPE = "DELETE FROM user_type "
            + "WHERE type_id = ?;";

    // Specialty
    private static final String BEGIN_TRANSACTION = "BEGIN TRANSACTION;";
    private static final String COMMIT_TRANSACTION = "ABORT TRANSACTION;";
    private static final String ABORT_TRANSACTION = "COMMIT TRANSACTION;";
    private static final String GET_SPECIMENS_FROM = "SELECT barcode "
            + "FROM shipping_info s, users u "
            + "WHERE s.shipper = u.username AND u.site_id = ?;";
    private static final String GET_SPECIMENS_TO = "SELECT barcode "
            + "FROM shipping_info " + "WHERE destination_id = ?;";
    private static final String GET_USERS_OF_SITE = "SELECT username FROM users WHERE site_id = ?;";
    private static final String GET_NOT_ARRIVED = "SELECT * FROM shipping_info si WHERE NOT status_id = 3;";

    // PreparedStatements
    private PreparedStatement addClientPS;
    private PreparedStatement addCourierPS;
    private PreparedStatement addHospitalPS;
    private PreparedStatement addItemPS;
    private PreparedStatement addToContainsPS;
    private PreparedStatement addShippingInfoPS;
    private PreparedStatement addSpecimenPS;
    private PreparedStatement addStatusPS;
    private PreparedStatement addUserPS;
    private PreparedStatement addUserTypePS;
    private PreparedStatement updateCheckListPairPS;
    private PreparedStatement updateClientSitePS;
    private PreparedStatement updateCourierPS;
    private PreparedStatement updateHospitalPS;
    private PreparedStatement updateItemPS;
    private PreparedStatement updateShippingInfoPS;
    private PreparedStatement updateSpecimenPS;
    private PreparedStatement updateStatusPS;
    private PreparedStatement updateUserPS;
    private PreparedStatement updateUserTypePS;
    private PreparedStatement getCheckListsPS;
    private PreparedStatement getClientSitePS;
    private PreparedStatement getCourierPS;
    private PreparedStatement getHospitalPS;
    private PreparedStatement getItemPS;
    private PreparedStatement getShippingInfoPS;
    private PreparedStatement getSpecimenPS;
    private PreparedStatement getContentsPS;
    private PreparedStatement getStatusPS;
    private PreparedStatement getUserPS;
    private PreparedStatement getUserTypePS;
    private PreparedStatement getAllClientSitesPS;
    private PreparedStatement getAllCouriersPS;
    private PreparedStatement getAllHospitalsPS;
    private PreparedStatement getAllItemsPS;
    private PreparedStatement getAllShippingInfosPS;
    private PreparedStatement getAllSpecimensPS;
    private PreparedStatement getAllStatusesPS;
    private PreparedStatement getAllUsersPS;
    private PreparedStatement getAllUserTypesPS;
    private PreparedStatement removeClientSitePS;
    private PreparedStatement removeCourierPS;
    private PreparedStatement removeHospitalPS;
    private PreparedStatement removeItemPS;
    private PreparedStatement removeShippingInfoPS;
    private PreparedStatement removeSpecimenPS;
    private PreparedStatement removeStatusPS;
    private PreparedStatement removeUserPS;
    private PreparedStatement removeUserTypePS;

    // Specialty
    private PreparedStatement beginTransactionPS;
    private PreparedStatement commitTransactionPS;
    private PreparedStatement abortTransactionPS;
    private PreparedStatement getSpecimensFromPS;
    private PreparedStatement getSpecimensToPS;
    private PreparedStatement getUsersOfSitePS;
    private PreparedStatement getNotArrivedPS;

    /**
     * Constructs a new PostgresHandler. The username and password here are
     * separate from the Jtacck System.
     * 
     * @param username
     *            : the username for the postgres database, this user name must
     *            have read/write/modify access to the database.
     * @param password
     *            : the password for the username for the database.
     * @param dbAddress
     *            : the server, port, and database name to access. Must be of
     *            the form "<<server>>:<<port>>/<<databasename>>" where << >>
     *            indicates a described variable
     * @throws SQLException
     *             If a database error occurs.
     */
    public PostgresHandler(String username, String password, String dbAddress)
            throws SQLException {
        this.user = username;
        this.password = password;
        this.dbAddress = dbAddress;
        open();
        initializePreparedStatements();
    }
  
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#open()
     */
    @Override
    synchronized public void open() throws SQLException {
        if (dbConn == null) {
            try {
                Class.forName("org.postgresql.Driver");
                dbConn = DriverManager.getConnection("jdbc:postgresql://"
                        + dbAddress, user, password);
            } catch (Exception e) {
                throw new SQLException("Postgres Database Driver Not Found");
            }
        }
        if (dbConn.isClosed()) {
            dbConn = DriverManager.getConnection("jdbc:postgresql://"
                    + dbAddress, user, password);
        }
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#close()
     */
    @Override
    synchronized public void close() throws SQLException {
        if (!isClosed()) {
            dbConn.close();
        }
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#isClosed()
     */
    @Override
    synchronized public boolean isClosed() throws SQLException {
        return dbConn.isClosed();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#login(String, String)
     */
    @Override
    synchronized public User login(String username, String password) {
        try {
            User u = getUserByID(username);
            if (u.password.equals(password)) {
                return u;
            }
        } catch (Exception e) {

        }
        return null;

    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(ClientSite cs) throws SQLException {
        addClientPS.clearParameters();
        addClientPS.setString(1, cs.name);
        addClientPS.setInt(2, cs.hospital.hospital_id);
        addClientPS.setArray(3, dbConn.createArrayOf("text", cs.instructions
                .toArray()));
        addClientPS.setString(4, cs.phone_number);
        addClientPS.executeUpdate();
    }
    
    /**
     * Creates all the prepared statements on initialization to speed up 
     * individual connection times.
     * @throws SQLException
     *          if a database error occurs
     */
    synchronized private void initializePreparedStatements() throws SQLException {
        addClientPS = dbConn.prepareStatement(ADD_CLIENT_SITE);
        addCourierPS = dbConn.prepareStatement(ADD_COURIER);
        addHospitalPS = dbConn.prepareStatement(ADD_HOSPITAL);
        addItemPS = dbConn.prepareStatement(ADD_ITEM,
                                            Statement.RETURN_GENERATED_KEYS);
        addToContainsPS = dbConn.prepareStatement(ADD_TO_CONTAINS);
        addShippingInfoPS = dbConn.prepareStatement(ADD_SHIPPING_INFO);
        addSpecimenPS = dbConn.prepareStatement(ADD_SPECIMEN);
        addStatusPS = dbConn.prepareStatement(ADD_STATUS);
        addUserPS = dbConn.prepareStatement(ADD_USER);
        addUserTypePS = dbConn.prepareStatement(ADD_USER_TYPE);
        updateCheckListPairPS = dbConn.prepareStatement(UPDATE_CHECKLIST_PAIR);
        updateClientSitePS = dbConn.prepareStatement(UPDATE_CLIENT_SITE);
        updateCourierPS = dbConn.prepareStatement(UPDATE_COURIER);
        updateHospitalPS = dbConn.prepareStatement(UPDATE_HOSPITAL);
        updateItemPS = dbConn.prepareStatement(UPDATE_ITEM);
        updateShippingInfoPS = dbConn.prepareStatement(UPDATE_SHIPPING_INFO);
        updateSpecimenPS = dbConn.prepareStatement(UPDATE_SPECIMEN);
        updateStatusPS = dbConn.prepareStatement(UPDATE_STATUS);
        updateUserPS = dbConn.prepareStatement(UPDATE_USER);
        updateUserTypePS = dbConn.prepareStatement(UPDATE_USER_TYPE);
        getCheckListsPS = dbConn.prepareStatement(GET_CHECKLISTS);
        getClientSitePS = dbConn.prepareStatement(GET_CLIENT_SITE);
        getCourierPS = dbConn.prepareStatement(GET_COURIER);
        getHospitalPS = dbConn.prepareStatement(GET_HOSPITAL);
        getItemPS = dbConn.prepareStatement(GET_ITEM);
        getShippingInfoPS = dbConn.prepareStatement(GET_SHIPPING_INFO);
        getSpecimenPS = dbConn.prepareStatement(GET_SPECIMEN);
        getContentsPS = dbConn.prepareStatement(GET_CONTENTS);
        getStatusPS = dbConn.prepareStatement(GET_STATUS);
        getUserPS = dbConn.prepareStatement(GET_USER);
        getUserTypePS = dbConn.prepareStatement(GET_USER_TYPE);
        getAllClientSitesPS = dbConn.prepareStatement(GET_ALL_CLIENT_SITES);
        getAllCouriersPS = dbConn.prepareStatement(GET_ALL_COURIERS);
        getAllHospitalsPS = dbConn.prepareStatement(GET_ALL_HOSPITALS);
        getAllItemsPS = dbConn.prepareStatement(GET_ALL_ITEMS);
        getAllShippingInfosPS = dbConn.prepareStatement(GET_ALL_SHIPPING_INFOS);
        getAllSpecimensPS = dbConn.prepareStatement(GET_ALL_SPECIMENS);
        getAllStatusesPS = dbConn.prepareStatement(GET_ALL_STATUSES);
        getAllUsersPS = dbConn.prepareStatement(GET_ALL_USERS);
        getAllUserTypesPS = dbConn.prepareStatement(GET_ALL_USER_TYPES);
        removeClientSitePS = dbConn.prepareStatement(REMOVE_CLIENT_SITE);
        removeCourierPS = dbConn.prepareStatement(REMOVE_COURIER);
        removeHospitalPS = dbConn.prepareStatement(REMOVE_HOSPITAL);
        removeItemPS = dbConn.prepareStatement(REMOVE_ITEM);
        removeShippingInfoPS = dbConn.prepareStatement(REMOVE_SHIPPING_INFO);
        removeSpecimenPS = dbConn.prepareStatement(REMOVE_SPECIMEN);
        removeStatusPS = dbConn.prepareStatement(REMOVE_STATUS);
        removeUserPS = dbConn.prepareStatement(REMOVE_USER);
        removeUserTypePS = dbConn.prepareStatement(REMOVE_USER_TYPE);

        // Specialized
        beginTransactionPS = dbConn.prepareStatement(BEGIN_TRANSACTION);
        commitTransactionPS = dbConn.prepareStatement(COMMIT_TRANSACTION);
        abortTransactionPS = dbConn.prepareStatement(ABORT_TRANSACTION);
        getUsersOfSitePS = dbConn.prepareStatement(GET_USERS_OF_SITE);
        getSpecimensFromPS = dbConn.prepareStatement(GET_SPECIMENS_FROM);
        getSpecimensToPS = dbConn.prepareStatement(GET_SPECIMENS_TO);
        getNotArrivedPS = dbConn.prepareStatement(GET_NOT_ARRIVED);

    }
    
    ///////////////////////
    // Database Commands //
    ///////////////////////

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(Courier c) throws SQLException {
        addCourierPS.clearParameters();
        addCourierPS.setString(1, c.user.username);
        List<Integer> x = new ArrayList<Integer>();
        for (ClientSite cs : c.route) {
            x.add(cs.client_id);
        }
        addCourierPS.setArray(2, dbConn.createArrayOf("int", x.toArray()));
        addCourierPS.setDouble(3, c.latitude);
        addCourierPS.setDouble(4, c.longitude);
        if (c.nextDest != null) {
            addCourierPS.setInt(5, c.nextDest.client_id);
        } else
            addCourierPS.setObject(5, null);
        addCourierPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(Hospital h) throws SQLException {
        addHospitalPS.clearParameters();
        addHospitalPS.setString(1, h.name);
        addHospitalPS.setString(2, h.address);
        addHospitalPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(ShippingInformation si) throws SQLException {
        Specimen sp = si.specimen;
        addRecord(sp);       
        addShippingInfoPS.clearParameters();
        addShippingInfoPS.setString(1, sp.barcode);
        addShippingInfoPS.setString(2, si.shipper.username);
        addShippingInfoPS.setInt(3, si.destination.client_id);
        addShippingInfoPS.setInt(4, si.status.status_id); // HARD CODE THIS???
        String cour = (si.courier == null) ? null : si.courier.user.username;
        addShippingInfoPS.setString(5, cour);
        
        try {
            addShippingInfoPS.executeUpdate();
        } catch (SQLException e) {
            // If there is a SQL Error, remove the specimen added
            removeRecord(si.specimen);
            throw e;
        }
        //dbConn.commit();
        //dbConn.setAutoCommit(true);
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(Specimen sp) throws SQLException {
        // TODO MAKE BEGIN TRANSACTION
        addSpecimenPS.clearParameters();
        addSpecimenPS.setString(1, sp.barcode);
        addSpecimenPS.setString(2, sp.description);
        addSpecimenPS.setString(3, sp.requirements);
        addSpecimenPS.executeUpdate();
        
        Collection<Item> temp = sp.contents;
        try {
            for (Item i : temp) {
                addItemToBarcode(i, sp.barcode);
            }
        
        } catch (SQLException e) {
            // If there is a SQL Error, remove the specimen added
            removeRecord(sp);
            throw e;
        }
    }
    /**
     * Helper function to speak to the database
     * 
     * @param h
     *            The Hospital to be added to the database
     */
    synchronized private void addItemToBarcode(Item i, String barcode) throws SQLException {
        addItemPS.clearParameters();
        addItemPS.setString(1, i.name);
        addItemPS.setString(2, i.capColor);
        addItemPS.executeUpdate();
        ResultSet rs = addItemPS.getGeneratedKeys();
        rs.next();

        addToContainsPS.clearParameters();
        addToContainsPS.setString(1, barcode);
        addToContainsPS.setInt(2, rs.getInt(1));
        addToContainsPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(Status st) throws SQLException {
        addStatusPS.clearParameters();
        addStatusPS.setString(1, st.name);
        addStatusPS.setString(2, st.description);
        addStatusPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(User u) throws SQLException {
        addUserPS.clearParameters();
        addUserPS.setString(1, u.username);
        addUserPS.setString(2, u.password);
        addUserPS.setString(3, u.fname);
        addUserPS.setString(4, u.lname);
        addUserPS.setInt(5, u.site.client_id);
        addUserPS.setInt(6, u.type.type_id);
        addUserPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#addRecord(records.Record)
     */
    @Override
    synchronized public void addRecord(UserType ut) throws SQLException {
        addUserTypePS.clearParameters();
        addUserTypePS.setString(1, ut.name);
        addUserTypePS.setString(2, ut.description);
        addUserTypePS.executeUpdate();
    }
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(CheckListPair clp) throws SQLException {
        updateCheckListPairPS.clearParameters();
        updateCheckListPairPS.setArray(1, dbConn.createArrayOf("text",
                clp.startingList.toArray()));
        updateCheckListPairPS.setArray(2, dbConn.createArrayOf("text",
                clp.endingList.toArray()));
        updateCheckListPairPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(ClientSite cs) throws SQLException {
        updateClientSitePS.clearParameters();
        updateClientSitePS.setString(1, cs.name);
        updateClientSitePS.setInt(2, cs.hospital.hospital_id);
        updateClientSitePS.setArray(3, dbConn.createArrayOf("text",
                cs.instructions.toArray()));
        updateClientSitePS.setString(4, cs.phone_number);
        updateClientSitePS.setInt(5, cs.client_id);
        updateClientSitePS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(Courier c) throws SQLException {
        // Creates a list of ID's and not objects
        List<Integer> clientIDs = new ArrayList<Integer>();
        for (ClientSite cs : c.route) {
            clientIDs.add(cs.client_id);
        }
        updateCourierPS.clearParameters();
        updateCourierPS.setArray(1, dbConn.createArrayOf("int", clientIDs
                .toArray()));
        updateCourierPS.setDouble(2, c.latitude);
        updateCourierPS.setDouble(3, c.longitude);
        if (c.nextDest != null) {
            updateCourierPS.setInt(4, c.nextDest.client_id);
        } else
            updateCourierPS.setObject(5, null);
        updateCourierPS.setString(5, c.user.username);
        
        updateCourierPS.executeUpdate();
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(Hospital h) throws SQLException {
        updateHospitalPS.clearParameters();
        updateHospitalPS.setString(1, h.name);
        updateHospitalPS.setString(2, h.address);
        updateHospitalPS.setInt(3, h.hospital_id);
        updateHospitalPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(ShippingInformation si) throws SQLException {
        // TODO THIS IS WRONG? ILLEGAL BARCODE?
        updateShippingInfoPS.clearParameters();
        updateShippingInfoPS.setString(1, si.shipper.username);
        updateShippingInfoPS.setInt(2, si.destination.client_id);
        updateShippingInfoPS.setInt(3, si.status.status_id);
        if (si.courier == null) {
            updateShippingInfoPS.setNull(4, java.sql.Types.VARCHAR);
        } else {
            updateShippingInfoPS.setString(4, si.courier.user.username);
        }
       
        updateShippingInfoPS.setString(5, si.specimen.barcode);
        updateShippingInfoPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(Specimen sp) throws SQLException {
        // TODO MAKE INTO TRANSACTION
        updateSpecimenPS.clearParameters();
        updateSpecimenPS.setString(1, sp.description);
        updateSpecimenPS.setString(2, sp.requirements);
        updateSpecimenPS.setString(3, sp.barcode);
        
        Specimen old = getSpecimenByID(sp.barcode);
        Collection<Item> keepThese = intersection(old.contents, sp.contents);
        Collection<Item> changeThese = difference(old.contents, sp.contents);
        
        for (Item i : changeThese) {
            if (old.contents.contains(i)) {
                removeRecord(i);
            } else {
                addItemToBarcode(i, sp.barcode);
            }
        }
        try {
            for (Item i : keepThese) {
                updateItem(i);
            }

            // Barcode will stay the same, so dont update the rest until everything
            // else has been updated
            updateSpecimenPS.executeUpdate();
        } catch (SQLException e) {
            // Error updating old one, reverting back
            changeRecord(old);
            throw e;
        }
    }
    /**
     * Returns the intersection of the two Collections where equality is based
     * solely on Item.item_id == other, not Item.equals(other).
     * 
     * @param oldColl
     *            A Collection to intersect with.
     * @param newColl
     *            A Collection to intersect with.
     * @return A new Collection of the intersection of the two given Collections
     */
    synchronized private Collection<Item> intersection(Collection<Item> oldColl,
            Collection<Item> newColl) {
        Set<Item> ret = new HashSet<Item>();
        for (Item i : oldColl) {
            for (Item j : newColl) {
                if (i.item_id == j.item_id) {
                    ret.add(j);
                }
            }
        }
        return ret;
    }
    /**
     * Returns the difference of the two collections where equality is based
     * solely on Item.item_id == other, not Item.equals(other)
     * 
     * @param oldColl
     *            A Collection of Items to find the difference
     * @param newColl
     *            A Collection of Items to find the difference
     * @return A new Collection of Items containing the difference of the two
     *         given Collections
     */
    synchronized private Collection<Item> difference(Collection<Item> oldColl,
            Collection<Item> newColl) {
        Set<Item> ret = new HashSet<Item>();
        Set<Item> temp = new HashSet<Item>(newColl);
        for (Item i : oldColl) {
            boolean mark = false;
            for (Item j : newColl) {
                if (i.item_id == j.item_id) {
                    mark = true;
                    temp.remove(j);
                    break;
                }
            }
            if (!mark)
                ret.add(i);
        }
        ret.addAll(temp);
        return ret;
    }
    
    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(Status st) throws SQLException {
        updateStatusPS.clearParameters();
        updateStatusPS.setString(1, st.name);
        updateStatusPS.setString(2, st.description);
        updateStatusPS.setInt(3, st.status_id);
        updateStatusPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(User u) throws SQLException {
        updateUserPS.clearParameters();
        updateUserPS.setString(1, u.password);
        updateUserPS.setString(2, u.fname);
        updateUserPS.setString(3, u.lname);
        updateUserPS.setInt(4, u.site.client_id);
        updateUserPS.setInt(5, u.type.type_id);
        updateUserPS.setString(6, u.username);
        updateUserPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#changeRecord(records.Record)
     */
    @Override
    synchronized public void changeRecord(UserType ut) throws SQLException {
        updateUserTypePS.clearParameters();
        updateUserTypePS.setString(1, ut.name);
        updateUserTypePS.setString(2, ut.description);
        updateUserTypePS.setInt(3, ut.type_id);
        updateUserTypePS.executeUpdate();
    }


    @Override
    synchronized public List<ClientSite> getAllClientSites() throws SQLException {
        List<ClientSite> list = new ArrayList<ClientSite>();
        ResultSet rs = getAllClientSitesPS.executeQuery();
        while (rs.next()) {
            list.add(new ClientSite(rs.getInt("client_id"), rs
                    .getString("name"), getHospitalByID(rs.getInt("hospital_id")),
                    Arrays.asList((String[]) rs.getArray("instructions")
                            .getArray()), rs.getString("phone_number")));
        }
        return list;
    }
    
    @Override
    synchronized public List<Courier> getAllCouriers() throws SQLException {
        List<Courier> list = new ArrayList<Courier>();
        ResultSet rs = getAllCouriersPS.executeQuery();
        while (rs.next()) {
            List<ClientSite> temp = new ArrayList<ClientSite>();
            Integer[] array = (Integer[]) rs.getArray("route").getArray();
        
            for (int cid : array) {
                temp.add(getClientSiteByID(cid));
            }
            list.add(new Courier(getUserByID(rs.getString("username")), temp, rs
                    .getDouble("lat_loc"), rs.getDouble("long_loc"),
                    getClientSiteByID(rs.getInt("next_destination"))));
        }
        return list;
    }
    @Override
    synchronized public List<Hospital> getAllHospitals() throws SQLException {
        List<Hospital> list = new ArrayList<Hospital>();
        ResultSet rs = getAllHospitalsPS.executeQuery();
        while (rs.next()) {
            list.add(new Hospital(rs.getInt("hospital_id"), rs
                    .getString("name"), rs.getString("address")));
        }
        return list;
    }
    @Override
    synchronized public List<Item> getAllItems() throws SQLException {
        List<Item> list = new ArrayList<Item>();
        ResultSet rs = getAllItemsPS.executeQuery();
        while (rs.next()) {
            list.add(new Item(rs.getInt("item_id"), rs.getString("name"), rs
                    .getString("cap_color")));
        }
        return list;
    }
    
    @Override
    synchronized public List<ShippingInformation> getAllShippingInformation()
            throws SQLException {
        List<ShippingInformation> list = new ArrayList<ShippingInformation>();
        ResultSet rs = getAllShippingInfosPS.executeQuery();
        while (rs.next()) {
            list.add(new ShippingInformation(getSpecimenByID(rs
                    .getString("barcode")), getUserByID(rs.getString("shipper")),
                    getClientSiteByID(rs.getInt("destination_id")), getStatusByID(rs
                            .getInt("status_id")), getCourierByID(rs
                            .getString("courier_id"))));
        }
        return list;
    }
    
    @Override
    synchronized public List<Specimen> getAllSpecimens() throws SQLException {
        List<Specimen> list = new ArrayList<Specimen>();
        ResultSet rs = getAllSpecimensPS.executeQuery();
        while (rs.next()) {
            String barcode = rs.getString("barcode");
            String description = rs.getString("description");
            String requirements = rs.getString("requirements");
        
            List<Item> contents = getContents(barcode);
        
            list.add(new Specimen(barcode, description, requirements,
                    Collections.unmodifiableList(contents)));
        }
        return list;
    }
    
    @Override
    synchronized public List<Status> getAllStatuses() throws SQLException {
        List<Status> list = new ArrayList<Status>();
        ResultSet rs = getAllStatusesPS.executeQuery();
        while (rs.next()) {
            list.add(new Status(rs.getInt("status_id"), rs.getString("name"),
                    rs.getString("description")));
        }
        return list;
    }
    @Override
    synchronized public List<User> getAllUsers() throws SQLException {
        List<User> list = new ArrayList<User>();
        ResultSet rs = getAllUsersPS.executeQuery();
        while (rs.next()) {
            list.add(new User(rs.getString("username"), rs
                    .getString("password"), rs.getString("fname"), rs
                    .getString("lname"), getClientSiteByID(rs.getInt("site_id")),
                    getUserTypeByID(rs.getInt("type_id"))));
        }
        return list;
    }
    @Override
    synchronized public List<UserType> getAllUserTypes() throws SQLException {
        List<UserType> list = new ArrayList<UserType>();
        ResultSet rs = getAllUserTypesPS.executeQuery();
        while (rs.next()) {
            list.add(new UserType(rs.getInt("type_id"), rs.getString("name"),
                    rs.getString("description")));
        }
        return list;
    }
    @Override
    synchronized public CheckListPair getCheckLists() throws SQLException {
        ResultSet record;
        record = getCheckListsPS.executeQuery();
        CheckListPair clp = null;
        if (record.next()) {
            clp = new CheckListPair(Arrays.asList((String[]) record.getArray(
                    "starting").getArray()), Arrays.asList((String[]) record
                    .getArray("ending").getArray()));
        
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return clp;
    }

    @Override
    synchronized public ClientSite getClientSiteByID(int id) throws SQLException {
        getClientSitePS.clearParameters();
        getClientSitePS.setInt(1, id);
        ResultSet record = getClientSitePS.executeQuery();
        ClientSite cs = null;
        if (record.next()) {
            cs = new ClientSite(record.getInt("client_id"), record
                    .getString("name"), getHospitalByID(record
                    .getInt("hospital_id")), Arrays.asList((String[]) record
                    .getArray("instructions").getArray()), record
                    .getString("phone_number"));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return cs;
    }

    /**
     * Returns a list of items that are contained by the given barcode
     * 
     * @param id
     *            The barcode of the specimen to get the contents of
     * @return A List of Items that are related to the barcode
     * @throws SQLException
     *             If there is a database error
     */
    synchronized private List<Item> getContents(String id) throws SQLException {
        getContentsPS.clearParameters();
        getContentsPS.setString(1, id);
        ResultSet contRec = getContentsPS.executeQuery();

        List<Item> contents = new ArrayList<Item>();

        while (contRec.next()) {
            if (!contRec.getString("barcode").equals(id))
                throw new SQLException("Multiple Specimens retrieved");
            contents.add(getItemByID(contRec.getInt("item_id")));
        }
        return contents;

    }

    @Override
    synchronized public Courier getCourierByID(String id) throws SQLException {
        getCourierPS.clearParameters();
        getCourierPS.setString(1, id);
        ResultSet record = getCourierPS.executeQuery();
        Courier cour = null;
        if (record.next()) {
            List<ClientSite> temp = new ArrayList<ClientSite>();
            Integer[] array = (Integer[]) record.getArray("route").getArray();

            for (int cid : array) {
                temp.add(getClientSiteByID(cid));
            }

            cour = new Courier(getUserByID(record.getString("username")), temp,
                    record.getDouble("lat_loc"), record.getDouble("long_loc"),
                    getClientSiteByID(record.getInt("next_destination")));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return cour;
    }

    @Override
    synchronized public Hospital getHospitalByID(int id) throws SQLException {
        getHospitalPS.clearParameters();
        getHospitalPS.setInt(1, id);
        ResultSet record = getHospitalPS.executeQuery();
        Hospital h = null;
        if (record.next()) {
            h = new Hospital(record.getInt("hospital_id"), record
                    .getString("name"), record.getString("address"));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return h;
    }

    @Override
    synchronized public Item getItemByID(int item_id) throws SQLException {
        getItemPS.clearParameters();
        getItemPS.setInt(1, item_id);
        ResultSet record = getItemPS.executeQuery();
        Item item = null;
        if (record.next()) {
            item = new Item(record.getInt("item_id"), record.getString("name"),
                    record.getString("cap_color"));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return item;
    }

    @Override
    synchronized public List<ShippingInformation> getNotArrived() throws SQLException {
        List<ShippingInformation> list = new ArrayList<ShippingInformation>();
        ResultSet rs = getNotArrivedPS.executeQuery();
        while (rs.next()) {
            list.add(new ShippingInformation(getSpecimenByID(rs
                    .getString("barcode")), getUserByID(rs.getString("shipper")),
                    getClientSiteByID(rs.getInt("destination_id")), getStatusByID(rs
                            .getInt("status_id")), getCourierByID(rs
                            .getString("courier_id"))));
        }
        return list;
    }

    @Override
    synchronized public ShippingInformation getShippingByID(String barcode)
            throws SQLException {
        getShippingInfoPS.clearParameters();
        getShippingInfoPS.setString(1, barcode);
        ResultSet record = getShippingInfoPS.executeQuery();
        ShippingInformation si = null;
        if (record.next()) {
            si = new ShippingInformation(getSpecimenByID(record
                    .getString("barcode")), getUserByID(record
                    .getString("shipper")), getClientSiteByID(record
                    .getInt("destination_id")), getStatusByID(record
                    .getInt("status_id")), getCourierByID(record
                    .getString("courier_id")));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return si;
    }

    @Override
    synchronized public ShippingInformation getShippingInfoFor(String barcode)
            throws SQLException {
        return getShippingByID(barcode);
    }

    @Override
    synchronized public Specimen getSpecimenByID(String id) throws SQLException {
        getSpecimenPS.clearParameters();
        getSpecimenPS.setString(1, id);
        ResultSet specRec = getSpecimenPS.executeQuery();
        Specimen spec = null;
        if (specRec.next()) {

            String barcode = specRec.getString("barcode");
            String description = specRec.getString("description");
            String requirements = specRec.getString("requirements");

            spec = new Specimen(barcode, description, requirements, Collections
                    .unmodifiableList(getContents(barcode)));
        }
        if (specRec.next())
            throw new SQLException("Too many records with same ID!");
        return spec;
    }

    /**
     * Returns a list of ShippingInformation whose specimens either shipped from
     * the given ClientSite based on the given boolean.
     * 
     * @param cs
     *            The ClientSite referenced for the ShippingInformation
     * @return A List of ShippingInformation, in no particular order.
     * @throws SQLException
     *             If a database error occurs.
     */
    synchronized private List<ShippingInformation> getSpecimensFrom(ClientSite cs)
            throws SQLException {
        getSpecimensFromPS.clearParameters();
        getSpecimensFromPS.setInt(1, cs.client_id);
        ResultSet rs = getSpecimensFromPS.executeQuery();

        List<ShippingInformation> list = new ArrayList<ShippingInformation>();

        while (rs.next()) {
            list.add(getShippingByID(rs.getString(1)));
        }

        return list;
    }

    /**
     * Returns a list of ShippingInformation whose specimens shipped to the
     * given ClientSite.
     * 
     * @param cs
     *            The ClientSite referenced for the ShippingInformation
     * @return A List of ShippingInformation, in no particular order.
     * @throws SQLException
     *             If a database error occurs.
     */
    synchronized private List<ShippingInformation> getSpecimensTo(ClientSite cs)
            throws SQLException {
        getSpecimensToPS.clearParameters();
        getSpecimensToPS.setInt(1, cs.client_id);
        ResultSet rs = getSpecimensToPS.executeQuery();

        List<ShippingInformation> list = new ArrayList<ShippingInformation>();

        while (rs.next()) {
            list.add(getShippingByID(rs.getString(1)));
        }

        return list;
    }

    @Override
    synchronized public Status getStatusByID(int id) throws SQLException {
        getStatusPS.clearParameters();
        getStatusPS.setInt(1, id);
        ResultSet record = getStatusPS.executeQuery();
        Status stat = null;
        if (record.next()) {
            stat = new Status(record.getInt("status_id"), record
                    .getString("name"), record.getString("description"));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return stat;
    }

    @Override
    synchronized public User getUserByID(String id) throws SQLException {
        getUserPS.clearParameters();
        getUserPS.setString(1, id);
        ResultSet record = getUserPS.executeQuery();
        User u = null;
        if (record.next()) {
            u = new User(record.getString("username"), record
                    .getString("password"), record.getString("fname"), record
                    .getString("lname"),
                    getClientSiteByID(record.getInt("site_id")), getUserTypeByID(record
                            .getInt("type_id")));
        }
        
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return u;
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#getUsersOf(int)
     */
    @Override
    synchronized public List<User> getUsersOf(ClientSite cs) throws SQLException {
        getUsersOfSitePS.clearParameters();
        getUsersOfSitePS.setInt(1, cs.client_id);
        ResultSet rs = getUsersOfSitePS.executeQuery();
        
        List<User> list = new ArrayList<User>();
        
        while (rs.next()) {
            list.add(getUserByID(rs.getString(1)));
        }
        return list;
    }

    @Override
    synchronized public UserType getUserType(String username) throws SQLException {
        User u = getUserByID(username);
        if (u == null)
            return null;
        return u.type;
    }

    @Override
    synchronized public UserType getUserTypeByID(int id) throws SQLException {
        getUserTypePS.clearParameters();
        getUserTypePS.setInt(1, id);
        ResultSet record = getUserTypePS.executeQuery();
        UserType ut = null;
        if (record.next()) {
            ut = new UserType(record.getInt("type_id"), record
                    .getString("name"), record.getString("description"));
        }
        if (record.next())
            throw new SQLException("Too many records with same ID!");
        return ut;
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(ClientSite cs) throws SQLException {
        removeClientSitePS.clearParameters();
        removeClientSitePS.setInt(1, cs.client_id);
        removeClientSitePS.executeUpdate();
    }    

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(Courier c) throws SQLException {
        removeCourierPS.clearParameters();
        removeCourierPS.setString(1, c.user.username);
        removeCourierPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(Hospital h) throws SQLException {
        removeHospitalPS.clearParameters();
        removeHospitalPS.setInt(1, h.hospital_id);
        removeHospitalPS.executeUpdate();
    }

    /**
     * Removes the given Item from the database and any associations to it.
     * 
     * @param i
     *            the Item to be removed from the database
     * @throws SQLException
     *             if an error occurs in the database
     */
    synchronized private void removeRecord(Item i) throws SQLException {
        removeItemPS.clearParameters();
        removeItemPS.setInt(1, i.item_id);
        removeItemPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(ShippingInformation si) throws SQLException {
        String barcode = si.specimen.barcode;
        removeRecord(si.specimen);
        removeShippingInfoPS.clearParameters();
        removeShippingInfoPS.setString(1, barcode);
        removeShippingInfoPS.executeUpdate();
    }

    /**
     * Removes the given Specimen from the database and any associations to it.
     * 
     * @param barcode
     *            the barcode of the Specimen to be removed from the database
     * @throws SQLException
     *             if an error occurs in the database
     */
    synchronized private void removeRecord(Specimen sp) throws SQLException {
        String barcode = sp.barcode;
        Specimen spec = getSpecimenByID(barcode);
        removeSpecimenPS.clearParameters();
        removeSpecimenPS.setString(1, barcode);
        for (Item i : spec.contents) {
            removeRecord(i);
        }
        removeSpecimenPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(Status st) throws SQLException {
        removeStatusPS.clearParameters();
        removeStatusPS.setInt(1, st.status_id);
        removeStatusPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(User u) throws SQLException {
        removeUserPS.clearParameters();
        removeUserPS.setString(1, u.username);
        removeUserPS.executeUpdate();
    }

    /*
     * (non-Javadoc)
     * 
     * @see database.DatabaseHandler#removeRecord(records.Record)
     */
    @Override
    synchronized public void removeRecord(UserType ut) throws SQLException {
        removeUserTypePS.clearParameters();
        removeUserTypePS.setInt(1, ut.type_id);
        removeUserTypePS.executeUpdate();
    }

    ////////////////////////////////
    // Specialized Getter Methods //
    ////////////////////////////////

    @Override
    synchronized public List<ShippingInformation> specimensFrom(ClientSite cs)
            throws SQLException {
        return getSpecimensFrom(cs);
    }

    @Override
    synchronized public List<ShippingInformation> specimensTo(ClientSite cs)
            throws SQLException {
        return getSpecimensTo(cs);
    }

    /**
     * Helper function to communicate with database.
     * 
     * @param is
     *            The Item to update in the database
     */
    synchronized private void updateItem(Item i) throws SQLException {
        updateItemPS.clearParameters();
        updateItemPS.setString(1, i.name);
        updateItemPS.setString(2, i.capColor);
        updateItemPS.setInt(3, i.item_id);
        updateItemPS.executeUpdate();
    }

    @Override
    synchronized public Date getLastUpdate(String barcode) throws SQLException {
        getShippingInfoPS.clearParameters();
        getShippingInfoPS.setString(1, barcode);
        ResultSet results = getShippingInfoPS.executeQuery();
        results.next();
        Date date = results.getTimestamp("time_updated");
        return date;
    }
    
    @Override
    synchronized public Date getFirstUpdate(String barcode) throws SQLException {
        getShippingInfoPS.clearParameters();
        getShippingInfoPS.setString(1, barcode);
        ResultSet results = getShippingInfoPS.executeQuery();
        results.next();
        Date date = results.getTimestamp("time_added");
        return date;
    }
}
